package com.dingli.DAO;

import com.dingli.utils.DataSourceUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class queryTicketDAO {
    private QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSource());

    public List<Map<String, Object>> queryTicket(int start, int step){
        return queryTicketByName(start, step, "");
    }

    public List<Map<String, Object>> queryTicketByName(int page, int step, String name){
        int p = (page - 1) * step;
        String sql = String.format(
            "SELECT l.lineName,l.passby,l.siteNum,t.restNumber,\n" +
            "group_concat(distinct concat(tl.levelName,'-',t.price,'-',t.ticketID) order by tl.levelName separator ',') as level_price\n" +
            "FROM ticket t \n" +
            "INNER JOIN line l ON t.lineID = l.lineID \n" +
            "INNER JOIN ticketlevel tl ON t.tlevel = tl.tlvID \n" +
            "WHERE l.lineName LIKE '%s' \n" +
            "GROUP BY l.lineName, l.passby, l.siteNum, t.restNumber\n" +
            "limit %d,%d;", "%"+name+"%", p, step);
        List<Map<String, Object>> resultsAsMapList = null;
        try {
            resultsAsMapList = runner.query(sql, new MapListHandler());
            return resultsAsMapList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<Map<String, Object>> queryTicketByCite(int start, int step, String from, String to){
        int p = (start - 1) * step;
        String sql = String.format(
            "SELECT l.lineName,l.passby,l.siteNum,t.restNumber,\n" +
            "group_concat(distinct concat(tl.levelName,'-',t.price,'-',t.ticketID) order by tl.levelName separator ',') as level_price\n" +
            "FROM ticket t \n" +
            "INNER JOIN line l ON t.lineID = l.lineID \n" +
            "INNER JOIN ticketlevel tl ON t.tlevel = tl.tlvID \n" +
            "WHERE l.passby LIKE '%s' \n" +
            "GROUP BY l.lineName, l.passby, l.siteNum, t.restNumber\n" +
            "limit %d,%d;", "%"+from.trim()+"%"+to.trim()+"%", p, step);
        List<Map<String, Object>> resultsAsMapList = null;
        try {
            resultsAsMapList = runner.query(sql, new MapListHandler());
            return resultsAsMapList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public int queryCount(){
        String sql =
            "select ceil(count(*) / 5) as pageCount from (\n" +
            "SELECT l.lineName\n" +
            "FROM ticket t \n" +
            "INNER JOIN line l ON t.lineID = l.lineID \n" +
            "INNER JOIN ticketlevel tl ON t.tlevel = tl.tlvID \n" +
            "GROUP BY l.lineName) as t1";
        try {
            BigDecimal pageCount = runner.query(sql, new ScalarHandler<BigDecimal>());
            return pageCount.intValue();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<Map<String, Object>> selectAllticket(){
        String sql =
            "SELECT t.ticketID,l.lineName,l.passby,t.price,t.restNumber,l.siteNum,tl.levelName\n" +
            "FROM ticket t \n" +
            "INNER JOIN line l ON t.lineID = l.lineID \n" +
            "INNER JOIN ticketlevel tl ON t.tlevel = tl.tlvID ";
        List<Map<String, Object>> resultsAsMapList = null;
        try {
            resultsAsMapList = runner.query(sql, new MapListHandler());
            return resultsAsMapList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}


